package com.exonline.back.controller.course;

import cn.hutool.core.util.IdUtil;
import com.exonline.commons.base.IBaseService;
import com.exonline.commons.entity.Courses;
import com.exonline.commons.entity.Direction;
import com.exonline.commons.entity.Subject;
import com.exonline.commons.dao.DaoImpl;
import com.exonline.commons.dao.IDao;
import org.apache.commons.dbutils.handlers.*;
import java.sql.SQLException;
import java.util.*;

/**
 * @desc课程管理模块M层
 * @author hbrj
 */
public class CoursesService implements IBaseService<Courses> {

    // 引入数据访问对象
    IDao<Courses> coursesDao = new DaoImpl<>();

    /**
     * @desc 1.查询所有课程信息+支持分页
     * @param pageSize 每页显示的课程数
     * @param pageNo 当前页码
     * @return 课程列表
     * @throws SQLException SQL异常
     */
    @Override
    public List<Courses> queryAll(int pageSize, int pageNo) throws SQLException {
        List<Courses> courses = coursesDao.queryWithPagingAndConditions("SELECT * FROM courses", null, pageNo, pageSize, new BeanListHandler<>(Courses.class));


        // 设置课程状态字段的值
        courses.forEach(s -> {s.setCstatus("0".equals(s.getCstatus())?"未发布":"已发布");});
        return courses;
    }
    /**
     * @desc 2.根据查询条件获取所有课程信息+支持分页
     * @param condition 查询条件
     * @param pageSize 每页显示的课程数
     * @param pageNo 当前页码
     * @return 课程列表
     * @throws SQLException SQL异常
     */
    public List<Courses> queryAll(String condition, int pageSize, int   pageNo) throws SQLException {
            // 1.获取课程和教师信息
            List<Courses> courses = coursesDao.queryWithPagingAndConditions("SELECT  c.*,t.tname FROM courses c join teacher t on t.id = c.tid", condition, pageNo, pageSize, new BeanListHandler<>(Courses.class));
            // 2.根据课程状态设置其中文描述，并处理课程评论和审核状态
            courses.stream().forEach(s -> {
                if (s.getCstatus().equals("0")) {
                    s.setCstatus("待审核");
                } else if (s.getCstatus().equals("1")) {
                    try {//2.1查询课程审核记录表,获取当前课程审核状态备注
                        List<Object[]> objects = coursesDao.queryForObject("SELECT comment from coursesreviewhistory where cid = ?", new ArrayListHandler(), s.getId());
                        if (objects != null && objects.size() > 0) {
                            s.setComment((String) objects.get(0)[0]);
                        }
                    } catch (SQLException e) {
                        throw new RuntimeException(e);
                    }
                    s.setCstatus("已通过");
                } else if (s.getCstatus().equals("2")) {
                    try {//2.2查询课程审核记录表,获取当前课程审核状态备注
                        List<Object[]> objects = coursesDao.queryForObject("SELECT comment from coursesreviewhistory where cid = ?", new ArrayListHandler(), s.getId());
                        if (objects != null && objects.size() > 0) {
                            s.setComment((String) objects.get(0)[0]);
                        }
                    } catch (SQLException e) {
                        throw new RuntimeException(e);
                    }
                    s.setCstatus("已驳回");
                }
            });
            // 3.计算每个课程的视频链接数量
            for (Courses cours : courses) {
                if (cours.getVideourls() != null) {
                    cours.setUrlscount(String.valueOf(cours.getVideourls().size()));
                }
            }
            return courses;
    }

    /**
     * @desc 3.查询所有课程的审核历史+支持分页
     * @param condition 查询条件
     * @param pageSize 每页显示的课程数
     * @param pageNo 当前页码
     * @return 课程列表
     * @throws SQLException SQL异常
     */
    public List<Courses> queryAllReviewHistory(String condition, int pageSize, int pageNo) throws SQLException {

            // 1.获取课程、教师和审核历史信息
            List<Courses> courses = coursesDao.queryWithPagingAndConditions("SELECT distinct c.*,t.tname,co.aid,co.aname FROM courses c join teacher t on t.id = c.tid left join coursesreviewhistory co on co.cid = c.id", condition, pageNo, pageSize, new BeanListHandler<>(Courses.class));

            // 2.根据课程状态设置其中文描述，并处理课程评论和审核状态
            courses.stream().forEach(s -> {
                if (s.getCstatus() != null && s.getCstatus().equals("0")) {
                    s.setCstatus("待审核");
                } else if (s.getCstatus().equals("1")) {
                    try {//2.1查询待审核课程记录
                        List<Object[]> objects = coursesDao.queryForObject("SELECT comment from coursesreviewhistory where cid = ?", new ArrayListHandler(), s.getId());
                        if (objects != null && objects.size() > 0) {
                            s.setComment((String) objects.get(0)[0]);
                        }
                    } catch (SQLException e) {
                        throw new RuntimeException(e);
                    }
                    s.setCstatus("已通过");
                } else if (s.getCstatus().equals("2")) {
                    try {//2.2
                        List<Object[]> objects = coursesDao.queryForObject("SELECT comment from coursesreviewhistory where cid = ?", new ArrayListHandler(), s.getId());
                        if (objects != null && objects.size() > 0) {
                            s.setComment((String) objects.get(0)[0]);
                        }
                    } catch (SQLException e) {
                        throw new RuntimeException(e);
                    }
                    s.setCstatus("已驳回");
                }
            });

            // 3.计算每个课程的视频链接数量
            for (Courses cours : courses) {
                if (cours.getVideourls() != null) {
                    cours.setUrlscount(String.valueOf(cours.getVideourls().size()));
                }
            }
            return courses;
    }

    /**
     * @desc 4.根据课程ID查询课程详细信息
     * @param id 课程ID
     * @return 课程对象
     * @throws SQLException SQL异常
     */
    @Override
    public Courses queryOne(String id) throws SQLException {
        // 1.查询课程和教师信息，以及课程的资源数量
        Courses courses = coursesDao.queryForObject("SELECT\n" +
                "\tc.*,t.tname as tname, \n" +
                "\t(select count(*) from coursesresource where cid = c.id) AS coursescount\n" +
                "FROM\n" +
                "\tcourses c\n" +
                "\tJOIN teacher t ON t.id = c.tid \n" +
                "WHERE\n" +
                "\tc.id = ?", new BeanHandler<Courses>(Courses.class), id);

        // 2.获取课程的视频链接，并将视频标题与链接关联起来
        List<String> urls = coursesDao.queryForObject("SELECT * FROM coursesresource WHERE cid = ?", new ColumnListHandler<>("videourl"), id);
        Map<String, Object> map = new HashMap<>();
        for (String url : urls) {
            //2.1截取url中的课程标题
            String title = url.substring(url.lastIndexOf("-") - 2, url.lastIndexOf("."));
            if (!map.containsKey(title)) {
                map.put(title, url);//2.2将课程标题存入到map中
            }
        }
        courses.setVideourls(map);  // 2.3设置课程的视频链接
        return courses;
    }

    /**
     * @desc 5.添加课程
     * @param obj 课程对象的相关信息
     * @throws SQLException SQL异常
     */
    @Override
    public void add(Object... obj) throws SQLException {
        coursesDao.updateWithTransaction("INSERT INTO courses VALUES(?,?,?,?,?,?,?,?,?,?)", obj);
    }

    /**
     * @desc 6.更新课程信息
     * @param obj 课程的状态、ID、评论、审核人ID等信息
     * @throws SQLException SQL异常
     */
    @Override
    public void update(Object... obj) throws SQLException {
        //1.课程下架操作,更新课程状态
        if (obj.length == 2) {
            coursesDao.updateWithTransaction("UPDATE courses SET cstatus = ? WHERE id =?", obj);
            return;
        }else{
            //2.课程上架时,更新课程状态
            String status = String.valueOf(obj[0]);
            String cid = String.valueOf(obj[1]);
            String comment = (String) obj[2];
            String aid = (String) obj[3];

                //2.1更新上架课程的审核状态
            coursesDao.updateWithTransaction("UPDATE courses SET cstatus = ? WHERE id = ?", status, cid);
                //2.2插入上架审核历史记录
            String hid = IdUtil.simpleUUID();
            coursesDao.updateWithTransaction("INSERT INTO coursesreviewhistory(id,cid,aid,status,comment) values(?,?,?,?,?)", hid, cid, aid, status, comment);
        }

    }

    /**
     * @desc 7.根据课程ID删除课程信息
     * @param id 课程ID
     * @throws SQLException SQL异常
     */
    @Override
    public void deleteById(String id) throws SQLException {
        coursesDao.updateWithTransaction("DELETE FROM courses WHERE id=?", id);
    }

    /**
     * @desc 8.根据课程ID删除审核历史
     * @param cid 课程ID
     * @throws SQLException SQL异常
     */
    public void deleteByIdReview(String cid) throws SQLException {
        coursesDao.updateWithTransaction("DELETE FROM coursesreviewhistory WHERE cid=?", cid);
    }

    /**
     * @desc 9.根据条件查询方向，并返回相关课程信息
     * @param condition 查询条件
     * @param pageSize 每页显示的课程数
     * @param pageNo 当前页码
     * @return 方向列表
     * @throws SQLException SQL异常
     */
    public List<Direction> structurequery(String condition, int pageSize, int pageNo) throws SQLException {
        //1.设置查询条件
        String sqlCondition = null;
        if ("free".equals(condition)) {
            sqlCondition = "cprice like " + "'0%'";
        } else if ("paid".equals(condition)) {
            sqlCondition = "cprice not like" + "'0%'";
        } else if ("tfree".equals(condition)) {
            sqlCondition = "cprice like " + "'0%'" + " AND cstatus = " + "'1'";
        } else if ("tpaid".equals(condition)) {
            sqlCondition = "cprice not like" + "'0%'" + " AND cstatus = " + "'1'";
        } else if ("ffree".equals(condition)) {
            sqlCondition = "cprice  like" + "'0%'" + " AND cstatus = " + "'0'";
        } else if ("fpaid".equals(condition)) {
            sqlCondition = "cprice  not like" + "'0%'" + " AND cstatus =" + "'0'";
        }

        //2.获取方向、学科和课程信息
        List<Direction> directions = coursesDao.queryWithPagingAndConditions("SELECT DISTINCT cdirection AS directionName FROM courses", null, pageNo, pageSize, new BeanListHandler<>(Direction.class));
        List<Subject> subjects = coursesDao.queryWithPagingAndConditions("SELECT DISTINCT csubject AS subjectName FROM courses", null, pageNo, pageSize, new BeanListHandler<>(Subject.class));
        List<Courses> courses = coursesDao.queryWithPagingAndConditions("SELECT c.*,t.tname FROM courses c join teacher t on t.id = c.tid", sqlCondition, pageNo, pageSize, new BeanListHandler<>(Courses.class));

        // 3.以方向为单位，返回课程信息
        for (Direction direction : directions) {
            String directionName = direction.getDirectionName();

            for (Courses cours : courses) {
                String courseSubjectName = cours.getCsubject();
                String courseDirectionName = cours.getCdirection();

                for (Subject subject : subjects) {
                    // 3.1如果学科匹配，添加课程
                    if (courseSubjectName.equals(subject.getSubjectName()) && !subject.getCourses().contains(cours)) {
                        subject.addCourses(cours);
                    }
                }

                // 3.2如果方向匹配，添加学科
                if (courseDirectionName.equals(directionName)) {
                    for (Subject subject : subjects) {
                        if (courseSubjectName.equals(subject.getSubjectName()) && !direction.getSubjects().contains(subject)) {
                            direction.addSubjects(subject);
                        }
                    }
                }
            }
        }
        return directions;
    }

    public String getRowCount(String sql) throws SQLException {
        int rowCount = coursesDao.queryCount(sql);
        return String.valueOf(rowCount);
    }
}
